package Dao;


import Impl.StudentDaoImpl;
import model.Student;
import model.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import util.C3P0Util;
import util.Md5Util;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class StudentDao implements StudentDaoImpl {
    @Override
    public boolean registerStudent(String userName, String password) {
        return false;
    }

    @Override
    public List<Student> getAllStudent() throws SQLException {
        String sql="select * from tbl_student";
        QueryRunner queryRunner=new QueryRunner(C3P0Util.getConnection());
        List<Student> students = queryRunner.query(sql,new BeanListHandler<Student>(Student.class));
        return students;
    }

    @Override
    public List<Student> getStudentByName(String name) {
        return null;
    }


    @Override
    public List<Student> getStudentByStuNo(String no) {
       String sql="select * from tbl_student where stuno=?";
       QueryRunner queryRunner=new QueryRunner(C3P0Util.getConnection());
       List<Student> student=null;
        try {
            student = queryRunner.query(sql,new BeanListHandler<Student>(Student.class),no);
            return student;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


        return student;
    }

    @Override
    public int updateStudent(Student s) {
        String sql="update tbl_student set stuname=?,classes=?,gender=?,department=?,tel=?,dormno=? where stuno=?";
        QueryRunner queryRunner=new QueryRunner(C3P0Util.getConnection());
        int result;
        try {
              result=queryRunner.update(sql,new Object[]{s.getStuName(),s.getClasses(),s.getGender(),s.getDepartment(),s.getTel(),s.getDormNo(),s.getStuNo()});
              return result;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return 0;
    }

    public boolean saveStudent(Student student){
        String sql = "insert into tbl_student(stuno,stuname) values(?,?)";
        QueryRunner query = new QueryRunner(C3P0Util.getConnection());
        int result=0;
        try {
            System.out.println(student.getStuNo());
            System.out.println(student.getStuName());
            result = query.update(sql,new Object[]{ student.getStuNo(), student.getStuName()});
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return result>0;
    }

    @Override
    public int deleteStudent(String stuNo) {
        String sql = "delete from tbl_student where stuno=?";
        QueryRunner query = new QueryRunner(C3P0Util.getConnection());
        try {
            int result=query.update(sql,stuNo);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }


    @Override
    public List<Student> getStudentByPaging(int currentPage, int pageSize) {
        String sql="select * from tbl_student where stuno limit ?,?";
        int start=(currentPage-1)*pageSize;
        QueryRunner queryRunner=new QueryRunner(C3P0Util.getConnection());
        List<Student> students= null;
        try {
            students = queryRunner.query(sql,new BeanListHandler<Student>(Student.class),new Object[]{start,pageSize});
            return students;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return null;
    }

    @Override
    public long totalRecords() {
        String sql="select count(*) as count from tbl_student";
        QueryRunner queryRunner=new QueryRunner(C3P0Util.getConnection());
        long result=0;
        try {
            List<Map<String,Object>> query=queryRunner.query(sql,new MapListHandler());
            result=(Long)query.get(0).get("count");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }
}

